# -*- coding: utf-8 -*-
"""
Created on Wed Nov 18 23:08:59 2020
key_split:key切分提取
ModelResult为model结果与Mysql交互的类
MR_to_sql:model结果的key入库
get_MR_result:查询model结果key 可自定义查询也可以全部查询
"""
import sys
sys.path.append("..")
from MYSQL import mysqldb 
from pandas import read_pickle

def key_split(key:str):
    kd = key.split('}')
    key_dict1 = eval(kd[0]+"}")
    key_dict2 = eval(kd[1] + "}")
    y_name, freq, end_datetime, test_len, periods = key_dict1["y_name"], key_dict1["freq"], key_dict1["end_datetime"], key_dict1["test_len"], key_dict1["periods"]
    score_func, mode, method, estimator, batch_size, loss_func, optimizer, learning_rate, epochs  = None, None, None, None, None, None, None, None, None
    n_features, x_time_step, prediction, y_time_step, valid_pct = key_dict2["n_features"], key_dict2["x_time_step"], key_dict2["prediction"], key_dict2["y_time_step"], key_dict2["valid_pct"]
    
    if 'filter' in key_dict1.keys():
        feature_selector = 'filter'
        score_func = key_dict1["score_func"]
        mode = key_dict1["mode"]
    elif 'correlation_filter' in key_dict1.keys():
        feature_selector = 'correlation_filter'
        method = key_dict1["method"]
    elif 'wrapper' in key_dict1.keys():
        feature_selector = 'wrapper'
        estimator = key_dict1["estimator"]
    elif "embedded" in key_dict1.keys():
        feature_selector = 'embedded'
        estimator = key_dict1["estimator"]
    else:
        raise Exception("Key is error!")
    
    sup_style = ['supervised_learning', 'unsupervised_learning', 'reinforcement_learning']
    for item in sup_style:
        if item in key_dict2.keys():
            supervised_style = item
    
    md_classification = ['regression', 'classification']
    for item in md_classification:
        if item in key_dict2.keys():
            model_classification = item
    
    if 'special_machine_learning' in key_dict2.keys():
        model_name = key_dict2["special_machine_learning"]
    elif 'deep_learning' in  key_dict2.keys():
        model_name =  key_dict2["deep_learning"]
        batch_size, loss_func, optimizer, learning_rate, epochs = key_dict2["batch_size"], key_dict2["loss_func"], key_dict2["optimizer"], key_dict2["learning_rate"] ,key_dict2["epochs"]
    else:
        raise Exception("Key is error!")
    
    return y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, n_features, x_time_step, prediction,\
        y_time_step, valid_pct, supervised_style, model_classification, model_name, batch_size, loss_func, optimizer, learning_rate, epochs



class ModelResult():
    def __init__(self, model_result_summary_table_name:str = "model_result_summary"):
        self.model_result_summary_table_name = model_result_summary_table_name
        
    @staticmethod
    def connect_MR_db():
        DB = mysqldb.MysqlDB(host = "192.168.3.150", user = "root", password = "tj2902",
                       db = "model_result_dl", port = 3306, charaset = 'UTF8')
        return DB

    def MR_to_sql(self, model_result:dict):
        db = self.connect_MR_db()
        for key, value in model_result.items():
            y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, n_features, x_time_step, prediction,\
                y_time_step, valid_pct, supervised_style, model_classification, model_name, batch_size, loss_func, optimizer, learning_rate, epochs = key_split(key)         
            sql = "INSERT INTO " + self.model_result_summary_table_name + "(model_key, y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator,\
                n_features, x_time_step, prediction, y_time_step, valid_pct, supervised_style, model_classification, model_name, batch_size, loss_func, optimizer, learning_rate, epochs) "
            sql += "VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
        
            db.insertdb(sql, data = (key, y_name, freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, n_features, x_time_step, prediction,\
                y_time_step, valid_pct, supervised_style, model_classification, model_name, batch_size, loss_func, optimizer, learning_rate, epochs))        
            
    def get_MR_result(self, customize:bool = False, y_name:tuple = None, freq:tuple = None, end_datetime:tuple = None, test_len:tuple = None,
                 periods:tuple = None, feature_selector:tuple = None, score_func:tuple = None, mode:tuple = None, method:tuple = None,\
                     estimator:tuple = None, n_features:tuple = None, x_time_step:tuple = None, prediction:bool = None, y_time_step:tuple = None,\
                         valid_pct:tuple = None, supervised_style:tuple = None, model_classification:tuple = None, model_name:tuple = None,\
                             batch_size:tuple = None, loss_func:tuple = None, optimizer:tuple = None, learning_rate:tuple = None, epochs:tuple = None):      
        db = self.connect_MR_db()
        if customize:
            sql = "SELECT DISTINCT model_key FROM " + self.model_result_summary_table_name + " WHERE "
            if y_name != None: sql += "y_name in (%s)"%(',' .join(["'%s'" % item for item in y_name]))
            select_name = ["freq", "end_datetime", "test_len", "periods", "feature_selector", "score_func", "mode", "method", "estimator", "n_features", "x_time_step", "prediction", "y_time_step",\
                           "valid_pct", "supervised_style", "model_classification", "model_name", "batch_size", "loss_func", "optimizer", "learning_rate", "epochs"]
            i = 0
            for items in [freq, end_datetime, test_len, periods, feature_selector, score_func, mode, method, estimator, n_features, x_time_step, prediction,\
                          y_time_step, valid_pct, supervised_style, model_classification, model_name, batch_size, loss_func, optimizer, learning_rate, epochs]:
                if items != None:
                    sql += " AND %s in (%s)"%(select_name[i], ',' .join(["'%s'" % item for item in items]))
                i += 1
        else:
            sql = "SELECT DISTINCT model_key FROM " + self.model_result_summary_table_name
        result = db.querydb(sql, data_format="dataframe")

        return result
    

if __name__ == '__main__':
    #model_result = read_pickle("..//..//output//trainer//trainer_2021_01_06_18_03_23_51.pkl")
    #ModelResult().MR_to_sql(model_result = model_result)
    #find_MR_result = ModelResult().get_MR_result()
    find_MR_result = ModelResult().get_MR_result(customize = True, y_name = ("PPI", periods = (18,24,30,36,42,48,60,66,72)))
